import getpath
import os
import logging
import csv
import myglobal
import config_json
import sample
import openpyxl
import xlrd
from xml.etree import ElementTree
import sqlite3
from PyQt5 import QtCore
from mydb import MyDB_Base
import datetime
import numpy as np
def in_list(one, l1):
    for i in range(len(l1)):
        logging.info(str(one)+str(l1[i]))
        if one in l1[i]:
            return i
    return -1
def remove_last(str1):
    arr = str1.split("//")
    return "//".join(arr[:-1])
def is_int(a_string):
    try:
        int(a_string)
        return True
    except ValueError:
        return False
def is_float(a_string):
    try:
        float(a_string)
        return True
    except TypeError:
        return False
    except ValueError:
        return False
def conv_t(d):
    (y, m, d) = d.split(":")
    if len(y) == 1:
        y = "0" + y
    if len(m) == 1:
        m = "0" + m
    if len(d) == 1:
        d = "0" + d
    return y + ":" + m + ":" + d


def conv_day(d):
    (y, m, d) = d.split("-")
    if len(m) == 1:
        m = "0" + m
    if len(d) == 1:
        d = "0" + d
    return y + "-" + m + "-" + d
def convert_time(t):
    r = t.replace("/", "-") + ":00"
    (d, t) = r.split(" ")
    return conv_day(d) + " " + conv_t(t)
class MyDb(MyDB_Base):
    def __init__(self,mainw):
        super().__init__(mainw)
        self.doublefmt="%.7f"
        self.properties = [
            {"ele":None,"delegate":None,'width':130,'attr': "sampleid",'visible':True,  'header': "id"},
            {"ele":None,"delegate":"floatEdit",'width':80,'attr': "mass",'visible':True,  'header': "质量"},
            {"ele":None,"delegate":"edit",'width':80,'attr': "beizhu",'visible':True,  'header': "备注"},
        ]
    def getejff(self):
        if self.ejff==None:
            self.ejff= myglobal.server.getlx_ejff("csonh")        
        return self.ejff
        # return ["红外线吸收法","红外法/热导法","红外吸收法/热导法","红外吸收法"]
        return "(charindex('红外',ejff)!=0 or charindex('热导',ejff)!=0)"
        return "('红外' in ejff or '热导' in ejff)"
    def getdepart(self):
        if self.depart==None:
            self.depart= myglobal.server.getlx_depart("csonh")        
        return self.depart
        # return ["固体分析组"]
    def getDataDays(self, v):
        fname=config_json.setting['database']
        if config_json.setting["sblx"] == "csi":
            data=self.import_csi(fname)
        elif config_json.setting["sblx"] == "ncs_cs_db":
            data=self.import_ncs_cs(fname,v)
        elif config_json.setting["sblx"] == "ncs_onh_db":
            data=self.import_ncs_onh(fname,v)
        elif config_json.setting["sblx"] == "leco836_xml":
            data=self.import_836_xml(fname)
        elif config_json.setting["sblx"] == "lecoh":
            data=self.import_csv_file_byfixed(fname)
        elif config_json.setting["sblx"] in ["ncs_on3000"]:
            data=self.import_on3000(fname)
        elif config_json.setting["sblx"] == "uni_cs":
            data=self.import_uni_cs(fname)
        elif config_json.setting["sblx"] == 'uni_on':
            data=self.import_uni_on(fname)
        elif config_json.setting["sblx"] == 'element_db':
            data=self.import_element_db(fname)
        else:
            data=self.import_csv_file_bytitle(fname)
        if len(data)>0:
            self.resetProperties(data[0])#.items.keys())
        return data 
    def import_ncs_cs(self, fname,v):
        logging.info(fname)
        de =QtCore.QDateTime.currentDateTime()
        de.setTime(QtCore.QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        db = de.addDays(-v)
        db.setTime(QtCore.QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        conn = sqlite3.connect(fname)
        c = conn.cursor()
        cmd = "select sampleid,sampleName,sampleweight,content10,content20,content30,content40  from result where sampleid between '" + \
            db_str + "' and '" + de_str + "' "
        c.execute(cmd)
        r=[]
        num=0
        for row in c:
            logging.info(row)
            lc=row[3]
            hc=row[4]
            ls=row[5]
            hs=row[6]
            if lc is None:
                lc=""
            if hc is None:
                hc=""
            if ls is None:
                ls=""
            if hs is None:
                hs=""
            # cmd = """insert into result(sampleid,m,LC,HC,LS,HS,time) values('%s','%s',%s,%s,%s,%s,'%s')""" % (
            #     row[1], row[2], lc,hc,ls,hs, row[0])
            # self.sql_cmd(cmd)
            s=sample.Sample()
            # logging.info(type(row[0]))
            s.sampleid=row[1]
            s.mass=row[2]
            s.num=num
            s.time=row[0].split(".")[0]
            s.items[("C","Low")]=lc
            s.items[("C","High")]=hc
            s.items[("S","Low")]=ls
            s.items[("S","High")]=hs
            r.append(s)
            num+=1
        c.close()
        # 关闭连接
        conn.close()
        return r
    def import_ncs_onh(self, fname,v):
        logging.info(fname)
        de =QtCore.QDateTime.currentDateTime()
        de.setTime(QtCore.QTime(23, 59, 59))
        de_str = de.toString("yyyy-MM-dd hh:mm:ss")
        db = de.addDays(-v)
        db.setTime(QtCore.QTime(0, 0, 0))
        db_str = db.toString("yyyy-MM-dd hh:mm:ss")
        conn = sqlite3.connect(fname)
        c = conn.cursor()
        cmd = "select sampleid,sampleName,sampleweight,content10,content20,content30,content40,content50,content60  from result where sampleid between '" + \
            db_str + "' and '" + de_str + "' "
        logging.info(cmd)
        c.execute(cmd)
        num=0
        r=[]
        for row in c:
            lo=row[3]
            ho=row[4]
            ln=row[5]
            hn=row[6]
            lh = row[7]
            hh = row[8]
            if lo is None:
                lo=""
            if ho is None:
                ho=""
            if ln is None:
                ln=""
            if hn is None:
                hn=""
            if lh is None:
                lh = ""
            if hh is None:
                hh = ""
            # cmd = """insert into result(sampleid,m,LO,HO,LN,HN,LH,HH,time) values('%s','%s',%s,%s,%s,%s,%s,%s,'%s')""" % (
            #     row[1], row[2], lo,ho,ln,hn, lh, hh, row[0])
            # self.sql_cmd(cmd)
            s=sample.Sample()
            s.sampleid=row[1]
            s.mass=row[2]
            s.num=num
            s.time=row[0].split(".")[0]#remov ms
            s.items[("O","Low")]=lo
            s.items[("O","High")]=ho
            s.items[("N","Low")]=ln
            s.items[("N","High")]=hn
            s.items[("H","Low")]=lh
            s.items[("H","High")]=hh
            r.append(s)
            num+=1
        c.close()
        # 关闭连接
        conn.close()
        return r
    def import_uni_on(self, fname):
        logging.info("import_uni_on")
        logging.info(fname)
        xl_book = xlrd.open_workbook(fname)
        table = xl_book.sheets()[0]
        data = []
        for i in range(table.nrows):
            r = []
            for j in range(table.ncols):
                r.append(table.cell(i, j).value)
            data.append(r)
        return self.import_data_bytitle(data)

    def import_uni_cs(self, fname):
        from openpyxl import load_workbook
        xl_book = load_workbook(filename=fname)
        table = xl_book.worksheets[0]
        logging.info(dir(table))
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
            data.append(r)
        return self.import_data_bytitle(data)#  self.uni_cs_data(data)

    def import_on3000(self, fname):
        from openpyxl import load_workbook
        xl_book = load_workbook(filename=fname)
        table = xl_book.worksheets[0]
        logging.info(dir(table))
        data = []
        for row in table.values:
            r = []
            for one in row:
                if one is not None:
                    r.append(one)
            data.append(r)
        data=data[1:]
        return self.import_data_bytitle(data)

    def import_element_db(self, fname):
        logging.info(fname)
        conn = sqlite3.connect(fname)
        c = conn.cursor()
        cmd = """select datetime((id-621355968000000000)/10000000.0,"unixepoch") as time,version,blob from AnalysisResults where time between '""" + \
            self.db_str + "' and '" + self.de_str + "' "
        logging.info(cmd)
        c.execute(cmd)
        for row in c:
            # logging.info(row)
            id1 = row[0]
            blob1 = row[2]
            str1 = blob1.decode("utf_8_sig")
            # str1=str1.split("//")[0]
            str1 = remove_last(str1)
            # logging.info(str1)
            r = json.loads(str1)
            # logging.info(r)
            # logging.info(id1, r['SampleId'], r['SampleWeight'] / 1000.0, r['Results'])
            cmd = """insert into result(sampleid,m,LC,HC,LS,HS,time) values('%s','%s',%s,%s,%s,%s,'%s')""" % (
                r['SampleId'], r['SampleWeight'] /
                1000.0, r['Results'][0], r['Results'][1], r['Results'][2],
                r['Results'][3], id1)
            self.sql_cmd(cmd)
        c.close()
        # 关闭连接
        conn.close()

    def import_836_xml(self, filename):
        tree = ElementTree.parse(filename)
        samples = tree.getroot()
        sample_count = len(samples)
        r=[]
        # title=["name","o","n","m"]
        num=0
        for i in range(sample_count):
            sample_set = samples[i]
            name = sample_set[1].attrib["Value"]
            parallel_nu = len(sample_set) - 6
            for j in range(parallel_nu):
                node=sample_set[6 + j]
                print(node,dir(node))
                o = node.find("Oxygen").attrib["Value"].split(" ")[0]
                n = node.find("Nitrogen").attrib["Value"].split(" ")[0]
                h = node.find("Hydrogen")
                if h is None:
                    h = ""
                else:
                    h = h.attrib["Value"].split(" ")[0]
                # h = sample_set[6 + j].find("Nitrogen").attrib["Value"].split(" ")[0]
                m = node.find("SampleMass").attrib["Value"].split(" ")[0]
                time = node.find("Data").attrib["Value"]
                d=time[:10]
                t=time[-8:]
                time=d+" "+t
                print(name,o,n,m)
                print(time,len(time))
                s=sample.Sample()
                s.sampleid=name
                s.mass=m
                s.num=num
                s.time=time
                s.items[("O","")]=o
                s.items[("N","")]=n
                s.items[("H","")]=h
                r.append(s)
                num+=1
        return r
    def import_csi(self, fname):
        data=self.csv_data(fname)
        num=0
        r=[]
        for one in data:
            logging.info("=====================")
            logging.info(one)
            # one=one[0].split('"\t')
            fs = []
            vs = []
            for f in config_json.setting[config_json.setting["sblx"]]["fields"]:
                logging.info(f)
                if is_int(f["at"]):
                    logging.info(f["at"])
                    fs.append( f["name"] )
                    if f["name"] == "m":
                        vs.append("%.4f"%(myglobal.myfloat(one[f["at"]].split(" ")[0]) / 1000.0))
                    else:
                        vs.append( one[f["at"]].strip() )
                else:
                    logging.info("not int")
                    at = in_list(f["at"], one)#%C or %S
                    logging.info(at)
                    if at == -1:
                        pass
                    else:
                        fs.append( f["name"] )
                        if f["name"] in ["LC", "LS"]:
                            vs.append(one[at].strip().split(" ")[0])
                        else:
                            vs.append( one[at].strip() )
                print(fs,vs)
            # s=sample.Sample()
            # s.num=num
            # for i in range(len(fs)):
            #     if fs[i]=="sampleid":
            #         s.sampleid=vs[i]
            #     elif fs[i]=="m":
            #         s.mass=vs[i]
            #     elif fs[i]=="time":
            #         s.time=vs[i]
            #     else:
            #         s.items[(fs[i][1],"")]=vs[i]
            if len(r)==0:
                r.append(fs)
            r.append(vs)
            num+=1
        return r
    def import_csv_file_byfixed(self, fname):
        data = self.csv_data(fname)
        #lecoh no title
        # title = data[0]
        # data = data[1:]
        # logging.info(title)
        r=[]
        num=0
        for one in data:
            logging.info("=====================")
            logging.info(one)
            if len(one) < 2:
                continue
            s=sample.Sample()
            s.num=num
            # fs = []
            # vs = []
            for f in config_json.setting[config_json.setting["sblx"]]["fields"]:
                try:
                    # vs.append("'" + one[f["at"]] + "'")
                    # fs.append("'" + f["name"] + "'")
                    if f["name"]=="sampleid":
                        s.sampleid=one[f["at"]].strip() #,"m","time"]:
                    elif f["name"]=="m":
                        s.mass=one[f["at"]].strip().split(" ")[0]
                    elif f["name"]=="time":
                        if type(one[f["at"]])==datetime.datetime:
                            s.time=one[f["at"]].strftime('%Y-%m-%d %H:%M:%S')
                        else:
                            s.time=one[f["at"]] #,"m","time"]:
                    else:
                        s.items[(f["name"][1],"")]=one[f["at"]].strip().split(" ")[0]
                except IndexError:
                    pass
            r.append(s)
            num+=1
        return r
    def csv_data(self,fname):
        file=open(fname, encoding=config_json.setting[config_json.setting["sblx"]]["encoding"])
        csv_reader = csv.reader(file)
        data = []
        for row in csv_reader:
            data.append(row)
        file.close()
        return data
    def import_text_bytitle(self, txt):
        logging.info(txt)
        data = []
        for row in txt.split("\n"):
            # logging.info(row)
            data.append(row.split("\t"))
        return self.import_data_bytitle(data)
    def import_csv_file_bytitle(self, fname):
        data = self.csv_data(fname)
        return self.import_data_bytitle(data)
    def import_data_bytitle(self,data):
        title = data[0]
        data = data[1:]
        logging.info(title)
        r=[]
        # logging.info(data)
        for f in config_json.setting[config_json.setting["sblx"]]["fields"]:
            f["at"] = in_list(f["title"], title)
            logging.info([f["name"],f["title"],f["at"]])
        num=0
        for one in data:
            logging.info(one)
            if len(one) < 2:
                continue
            s=sample.Sample()
            s.num=num
            for f in config_json.setting[config_json.setting["sblx"]]["fields"]:
                if f["at"] != -1:
                    # dict1[f["name"]]=one[f["at"]]
                    if f["name"]=="sampleid":
                        s.sampleid=one[f["at"]].strip() #,"m","time"]:
                    elif f["name"]=="m":
                        if config_json.setting["sblx"] == "ncs_on3000":
                            s.mass=one[f["at"]]
                        elif config_json.setting["sblx"] in ["uni_cs","uni_on"]:
                            s.mass="%.4f" % (myglobal.myfloat(one[f["at"]])/1000.0)
                        else:
                            s.mass=one[f["at"]].strip().split(" ")[0]
                    elif f["name"]=="time":
                        if config_json.setting["sblx"]=="uni_on":
                            xl_date = one[f["at"]]
                            date1 = xlrd.xldate_as_datetime(xl_date, 0)
                            s.time=date1.strftime("%Y-%m-%d %H:%M:%S")
                        elif type(one[f["at"]])==datetime.datetime:#config_json.setting["sblx"] in ["uni_cs","uni_on"]:
                            s.time=one[f["at"]].strftime('%Y-%m-%d %H:%M:%S')
                        else:
                            s.time=one[f["at"]] #,"m","time"]:
                    else:
                        if config_json.setting["sblx"] == "horibah":
                            conc=one[f["at"]].strip()
                            s.items[(f["name"][1],"")]=myglobal.myfloat(conc.split(" ")[0])/10000.0
                            logging.info([one[f["at"]],s.items])
                        elif config_json.setting["sblx"] == "ncs_on3000":
                            if f["name"][0]=="L":
                                s.items[(f["name"][1],"low")]=myglobal.myfloat(one[f["at"]])*100
                            else:
                                s.items[(f["name"][1],"high")]=myglobal.myfloat(one[f["at"]])*100
                        elif config_json.setting["sblx"] in ["uni_cs","uni_on"]:
                            if f["name"][0]=="L":
                                s.items[(f["name"][1],"low")]=one[f["at"]]
                            else:
                                s.items[(f["name"][1],"high")]=one[f["at"]]
                        else:
                            s.items[(f["name"][1],"")]=one[f["at"]].strip().split(" ")[0]
            if s.sampleid=="" or ("名称" in s.sampleid):
                continue
            num+=1
            logging.info([s.items.keys(),s.items.values()])
            r.append(s)
        # self.resetProperties(s.items.keys())
        return r
    def resetProperties(self,items):
        self.properties = [
            {"ele":None,"delegate":None,'width':30,'attr': "num",'visible':True,  'header': "序号"},
            {"ele":None,"delegate":None,'width':200,'attr': "sampleid",'visible':True,  'header': "id"},
            {"ele":None,"delegate":None,'width':80,'attr': "mass",'visible':True,  'header': "质量"},
            # {"ele":None,"delegate":"floatEdit",'width':80,'attr': "volume",'visible':True,  'header': "体积"},
            {"ele":None,"delegate":"edit",'width':80,'attr': "beizhu",'visible':True,  'header': "备注"},
            {"ele":None,"delegate":None,'width':160,'attr': "time",'visible':True,  'header': "time"},
        ]
        for item in items:
            one={"ele":item,"delegate":"double",'width':80,'attr': item,'visible':True,  'header': item}
            self.properties.append(one)

    def output_xlsx(self, filepath):
        n = self.mainw.model2.rowCount()
        if n == 0:
            return
        self.mainw.export_num+=1
        filepath = filepath +"_"+str(self.mainw.export_num)+ ".xlsx"
        # 1. 创建文件对象（指定文件名，模式，编码方式）a模式 为 下次写入在这次的下一行
        wb = openpyxl.Workbook()
        # 3. 构建列表头
        row = ["id", "m"]
        for f in self.properties:
            if f["visible"] and f["ele"]!=None:
                row.append(f["ele"][0])
        row.append("备注")
        row.append("time")
        ws = wb.active
        ws.append(row)
        for row_idx in range(n):
            record = self.mainw.model2.getObjectRow(row_idx)
            row = [record.sampleid,record.mass]
            for f in self.properties:
                if f["visible"] and f["ele"]!=None:
                    conc = myglobal.myfloat(getattr(record,f["attr"]))
                    conc = self.doublefmt % conc
                    row.append(conc)
            row.append(record.beizhu)
            row.append(record.time)
            ws.append(row)
        self.output_append_common(ws)
        wb.save(filepath)
        self.mainw.ui.textEdit.insertPlainText(filepath + "\n")
        scrollbar = self.mainw.ui.textEdit.verticalScrollBar()
        scrollbar.setSliderPosition(scrollbar.maximum())                      
def test_sblx(sblx,fname):
    file = os.path.join(getpath.getpath(),"data",fname)
    config_json.read_setting()
    config_json.setting['sblx'] =sblx
    config_json.setting['database'] = file
    db = MyDb(None)
    objs=db.getDataDays(200)
    print(objs[0])
    print(objs[1])
    print(objs[-1])
    config_json.write_setting()
def test844():
    # test_sblx("844","844.csv")
    # test_sblx("844","cd_844.csv")
    # test_sblx("uni_on","uni_on.xls")
    # test_sblx("uni_cs","uni_cs.xlsx")
    # test_sblx("930","930.csv")
    # test_sblx("leco836_csv","cd_836.csv")
    # test_sblx("lecoh","lecoh.csv")
    # test_sblx("leco836_xml","836.xml")
    # test_sblx("horibah","horibah.csv")
    test_sblx("csi","csi.XLS")
if __name__=="__main__":
    test844()